﻿Imports System.Data.SqlClient
Imports System.Data
Public Class DangTinDAO
    'Public Function GetItemIDMaTin(ByVal matin As Integer) As DataTable
    '    Dim cnn As New SqlConnection
    '    Dim dataProvider As New DataProvider
    '    Dim da As SqlDataAdapter
    '    Dim strCon As String = "select dt.MaDangTin, dt.TieuDeTin, ln.TenLoaiNha, dc.MaDiaChi, dc.SoNha, dc.Duong, dc.Phuong, dc.Quan, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TinhTrang, dt.ThoiGianDangTin, dt.ThoiGianDongTin, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGTIN dt,LOAINHA ln,DIACHI dc where dt.MaLoaiNha = ln.MaLoaiNha and dt.MaDiaChi=dc.MaDiaChi and dt.MaChuNha = " + machunha
    '    cnn = dataProvider.ConnectionData()
    '    da = New SqlDataAdapter(strCon, cnn)
    '    Dim data As New DataTable
    '    da.Fill(data)
    '    Return data
    'End Function
    Public Function SelectID(ByVal matin As Integer) As DangTinDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGTIN where MaDangTin = " + matin.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Dim dt As New DangTinDTO
        Try
            dt.MaDiaChi = Convert.ToInt32(data.Rows(0)("MaDiaChi").ToString())
            dt.MaChuNha = Convert.ToInt32(data.Rows(0)("MaChuNha").ToString())
            dt.MaLoaiNha = Convert.ToInt32(data.Rows(0)("MaLoaiNha").ToString())
            dt.MaLoaiTin = Convert.ToInt32(data.Rows(0)("MaLoaiTin").ToString())
            dt.MaDangTin = Convert.ToInt32(data.Rows(0)("MaDangTin").ToString())
            dt.DienTich = Convert.ToInt32(data.Rows(0)("DienTich").ToString())
            dt.SucChua = Convert.ToInt32(data.Rows(0)("SucChua").ToString())
            dt.TieuDe = data.Rows(0)("TieuDeTin").ToString()
            dt.OChungChu = Convert.ToInt32(data.Rows(0)("OChungChu").ToString())
            dt.OGhep = Convert.ToInt32(data.Rows(0)("OGhep").ToString())
            dt.ChoNuThue = Convert.ToInt32(data.Rows(0)("ChoNuThue").ToString())
            dt.ChoNamThue = Convert.ToInt32(data.Rows(0)("ChoNamThue").ToString())
            dt.ChoNauAn = Convert.ToInt32(data.Rows(0)("ChoNauAn").ToString())
            dt.GanCho = Convert.ToInt32(data.Rows(0)("GanCho").ToString())
            dt.GiaThue = Convert.ToInt32(data.Rows(0)("GiaThue").ToString())
            dt.ThoiGianDangTin = data.Rows(0)("ThoiGianDangTin").ToString()
            dt.ThoiGianDongTin = data.Rows(0)("ThoiGianDongTin").ToString()
            dt.GanTramXeBuyt = Convert.ToInt32(data.Rows(0)("GanTramXeBuyt").ToString())
        Catch ex As Exception

        End Try
        Return dt
    End Function
    Public Function SeachTin(ByVal tenloainha As String, ByVal tenkhuvuc As String, ByVal giatu As String, ByVal giaden As String) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select dt.MaDangTin, dt.TieuDeTin, ln.TenLoaiNha, dc.MaDiaChi, dc.SoNha, dc.Duong, dc.Phuong, dc.Quan, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TinhTrang, dt.ThoiGianDangTin, dt.ThoiGianDongTin, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGTIN dt, DIACHI dc, LOAINHA ln  where dt.MaDiaChi = dc.MaDiaChi And dt.MaLoaiNha = ln.MaLoaiNha And ln.TenLoaiNha = N'" + tenloainha.ToString() + "' And dc.Quan = N'" + tenkhuvuc.ToString() + "' And dt.GiaThue>=" + giatu.ToString() + " And dt.GiaThue<=" + giaden.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    'Public Function SeachKhuVuc(ByVal tukhoa As String) As DataTable
    '    Dim cnn As New SqlConnection
    '    Dim dataProvider As New DataProvider
    '    Dim da As SqlDataAdapter
    '    Dim strCon As String = "select dt.MaDangTin, dt.TieuDeTin, ln.TenLoaiNha, dc.MaDiaChi, dc.SoNha, dc.Duong, dc.Phuong, dc.Quan, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TinhTrang, dt.ThoiGianDangTin, dt.ThoiGianDongTin, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGTIN dt,DIACHI dc where dt.MaDiaChi=dc.MaDiaChi and dc.Quan like '%" + tukhoa + "%'"
    '    cnn = dataProvider.ConnectionData()
    '    da = New SqlDataAdapter(strCon, cnn)
    '    Dim data As New DataTable
    '    Try
    '        da.Fill(data)
    '    Catch ex As Exception
    '        MessageBox.Show("Không có kết quả!", "Thông báo")
    '    End Try
    '    Return data
    'End Function
    Public Function GetItemID(ByVal machunha As String) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select dt.MaDangTin, dt.TieuDeTin, ln.TenLoaiNha, dc.MaDiaChi, dc.SoNha, dc.Duong, dc.Phuong, dc.Quan, dt.GiaThue, dt.DienTich, dt.SucChua, dt.TinhTrang, dt.ThoiGianDangTin, dt.ThoiGianDongTin, dt.OGhep, dt.OChungChu,dt.GanCho,dt.GanTramXeBuyt,dt.ChoNuThue,dt.ChoNamThue,dt.ChoNauAn,dt.Internet,dt.TruyenHinhCap from DANGTIN dt,LOAINHA ln,DIACHI dc where dt.MaLoaiNha = ln.MaLoaiNha and dt.MaDiaChi=dc.MaDiaChi and dt.MaChuNha = " + machunha
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGTIN"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function GetItemTinhTrang(ByVal trangthaiTin As Integer) As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from DANGTIN where TinhTrang= " + trangthaiTin.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Sub Insert(ByVal nha As DangTinDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertDANGTIN @TieuDeTin,@MaLoaiTin,@MaLoaiNha,@MaChuNha,@MaDiaChi,@GiaThue,@DienTich,@SucChua,@TinhTrang,@ThoiGianDangTin,@ThoiGianDongTin,@OGhep,@OChungChu,@GanCho,@GanTramXeBuyt,@ChoNuThue,@ChoNamThue,@ChoNauAn,@Internet,@TruyenHinhCap,@MaDangTin"
            Dim para As SqlParameter = cmd.Parameters.Add("@TieuDeTin", SqlDbType.NVarChar)
            para.Value = nha.TieuDe
            para = cmd.Parameters.Add("@MaLoaiTin", SqlDbType.Int)
            para.Value = nha.MaLoaiTin
            para = cmd.Parameters.Add("@MaLoaiNha", SqlDbType.Int)
            para.Value = nha.MaLoaiNha
            para = cmd.Parameters.Add("@MaChuNha", SqlDbType.Int)
            para.Value = nha.MaChuNha
            para = cmd.Parameters.Add("@MaDiaChi", SqlDbType.Int)
            para.Value = nha.MaDiaChi
            para = cmd.Parameters.Add("@GiaThue", SqlDbType.Int)
            para.Value = nha.GiaThue
            para = cmd.Parameters.Add("@DienTich", SqlDbType.Float)
            para.Value = nha.DienTich
            para = cmd.Parameters.Add("@SucChua", SqlDbType.Int)
            para.Value = nha.SucChua
            para = cmd.Parameters.Add("@TinhTrang", SqlDbType.Int)
            para.Value = nha.TinhTrang
            para = cmd.Parameters.Add("@ThoiGianDangTin", SqlDbType.VarChar)
            para.Value = nha.ThoiGianDangTin
            para = cmd.Parameters.Add("@ThoiGianDongTin", SqlDbType.VarChar)
            para.Value = nha.ThoiGianDongTin
            para = cmd.Parameters.Add("@OGhep", SqlDbType.Int)
            para.Value = nha.OGhep
            para = cmd.Parameters.Add("@OChungChu", SqlDbType.Int)
            para.Value = nha.OChungChu
            para = cmd.Parameters.Add("@GanCho", SqlDbType.Int)
            para.Value = nha.GanCho
            para = cmd.Parameters.Add("@GanTramXeBuyt", SqlDbType.Int)
            para.Value = nha.GanTramXeBuyt
            para = cmd.Parameters.Add("@ChoNuThue", SqlDbType.Int)
            para.Value = nha.ChoNuThue
            para = cmd.Parameters.Add("@ChoNamThue", SqlDbType.Int)
            para.Value = nha.ChoNamThue
            para = cmd.Parameters.Add("@ChoNauAn", SqlDbType.Int)
            para.Value = nha.ChoNauAn
            para = cmd.Parameters.Add("@Internet", SqlDbType.Int)
            para.Value = nha.Internet
            para = cmd.Parameters.Add("@TruyenHinhCap", SqlDbType.Int)
            para.Value = nha.TruyenHinhCap
            para = cmd.Parameters.Add("@MaDangTin", SqlDbType.Int)
            para.Value = nha.MaDangTin
            cmd.ExecuteNonQuery()
            'MessageBox.Show("Tin đã được tạo thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Delete(ByVal nha As DangTinDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteDANGTIN @MaDangTin"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaDangTin", SqlDbType.Int)
            para.Value = nha.MaDangTin
            cmd.ExecuteNonQuery()
            'MessageBox.Show("Tin đã được xóa thành công", "Thông báo")
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Update(ByVal nha As DangTinDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateDANGTIN @MaDangTin, @TieuDeTin,@MaLoaiTin,@MaLoaiNha,@MaChuNha,@MaDiaChi,@GiaThue,@DienTich,@SucChua,@TinhTrang,@OGhep,@OChungChu,@GanCho,@GanTramXeBuyt,@ChoNuThue,@ChoNamThue,@ChoNauAn,@Internet,@TruyenHinhCap"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaDangTin", SqlDbType.Int)
            para.Value = nha.MaDangTin
            para = cmd.Parameters.Add("@TieuDeTin", SqlDbType.NVarChar)
            para.Value = nha.TieuDe
            para = cmd.Parameters.Add("@MaLoaiTin", SqlDbType.Int)
            para.Value = nha.MaLoaiTin
            para = cmd.Parameters.Add("@MaLoaiNha", SqlDbType.Int)
            para.Value = nha.MaLoaiNha
            para = cmd.Parameters.Add("@MaChuNha", SqlDbType.Int)
            para.Value = nha.MaChuNha
            para = cmd.Parameters.Add("@MaDiaChi", SqlDbType.Int)
            para.Value = nha.MaDiaChi
            para = cmd.Parameters.Add("@GiaThue", SqlDbType.Int)
            para.Value = nha.GiaThue
            para = cmd.Parameters.Add("@DienTich", SqlDbType.Float)
            para.Value = nha.DienTich
            para = cmd.Parameters.Add("@SucChua", SqlDbType.Int)
            para.Value = nha.SucChua
            para = cmd.Parameters.Add("@TinhTrang", SqlDbType.Int)
            para.Value = nha.TinhTrang
            'para = cmd.Parameters.Add("@ThoiGianDangTin", SqlDbType.VarChar)
            'para.Value = nha.ThoiGianDangTin
            'para = cmd.Parameters.Add("@ThoiGianDongTin", SqlDbType.VarChar)
            para.Value = nha.ThoiGianDongTin
            para = cmd.Parameters.Add("@OGhep", SqlDbType.Int)
            para.Value = nha.OGhep
            para = cmd.Parameters.Add("@OChungChu", SqlDbType.Int)
            para.Value = nha.OChungChu
            para = cmd.Parameters.Add("@GanCho", SqlDbType.Int)
            para.Value = nha.GanCho
            para = cmd.Parameters.Add("@GanTramXeBuyt", SqlDbType.Int)
            para.Value = nha.GanTramXeBuyt
            para = cmd.Parameters.Add("@ChoNuThue", SqlDbType.Int)
            para.Value = nha.ChoNuThue
            para = cmd.Parameters.Add("@ChoNamThue", SqlDbType.Int)
            para.Value = nha.ChoNamThue
            para = cmd.Parameters.Add("@ChoNauAn", SqlDbType.Int)
            para.Value = nha.ChoNauAn
            para = cmd.Parameters.Add("@Internet", SqlDbType.Int)
            para.Value = nha.Internet
            para = cmd.Parameters.Add("@TruyenHinhCap", SqlDbType.Int)
            para.Value = nha.TruyenHinhCap
            cmd.ExecuteNonQuery()
            MessageBox.Show("Tin đã được cập nhật thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
